//
//  CAIFMDB.m
//  Runner
//
//  Created by simon on 2021/8/31.
//

#import "CAIFMDB.h"
#import <FMDB/FMDB.h>
#import <objc/runtime.h>
#import <YYModel/YYModel.h>
#import "NSData+CAIStorageBase64.h"


/** SQLite五种数据类型 */
static NSString *const SQTEXT = @"TEXT";

///值是一个带符号的整数，根据值的大小存储在1、2、3、4、6或8字节中。
static NSString *const SQINTEGER = @"INTEGER";

///值是一个浮点值
static NSString *const SQREAL = @"REAL";

///值是一个blob数据，完全根据它的输入存储
static NSString *const SQBLOB = @"BLOB";

///值是一个NULL值
static NSString *const SQNULL = @"NULL";

#define SQ_PRIMARY_KEY  @"PRIMARY KEY"
static NSString *const SQPrimaryId = @"CAIID";

#ifndef WS
#define WS(weakSelf)  __weak __typeof(&*self)weakSelf = self;
#endif

@interface CAIFMDB ()

@property (nonatomic, copy) NSString *dbPath;
@property (nonatomic, strong) FMDatabase *db;
@property (nonatomic, strong)FMDatabaseQueue *dbQueue;
/// 返回创建表的SQL语句
/// @param tableName 表的名称，不能为空；
/// @param parameters SQLite表的key-value，key是column定义名，value是数据类型；如果为nil或空，则创建的是空表，如@{@"name":@"TEXT",@"age":@"INTEGER"}；
/// @param nameArr 不允许model或dic里的key生成表的字段
- (NSString *)createTableSQLWithTable:(NSString *)tableName
                           dictionary:(nullable NSDictionary *)parameters
                          excludeName:(nullable NSArray<NSString *> *)nameArr;
@end



@implementation CAIFMDB

#pragma mark - Singleton, init, dealloc

- (FMDatabaseQueue *)dbQueue
{
    if (!_dbQueue) {
        FMDatabaseQueue *fmdb = [FMDatabaseQueue databaseQueueWithPath:_dbPath];
        self.dbQueue = fmdb;
        [_db close];
        self.db = [fmdb valueForKey:@"_db"];
    }
    return _dbQueue;
}

+ (nullable instancetype)sharedInstance
{
    static CAIFMDB *instance = nil;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        instance = [[self alloc] init];
    });
    return instance;
}


- (nullable instancetype)init
{
    return [self initWithDBName:@"CAIFMDB.db"];
}


- (nullable instancetype)initWithDBName:(nullable NSString *)dbName
{
    return [self initWithDBName:dbName path:nil];
}

- (nullable instancetype)initWithDBName:(nullable NSString *)dbName path:(nullable NSString *)dbPath
{
    if (self = [super init]) {
        if (!dbName) {
            dbName = @"CAIFMDB.db";
        }
        NSString *path;
        if (!dbPath) {
            NSString *docsPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];
            path = [docsPath stringByAppendingPathComponent:dbName];
        }else{
            path = [dbPath stringByAppendingPathComponent:dbName];
        }
        FMDatabase *fmdb = [FMDatabase databaseWithPath:path];
        //如果数据库不存在，则创建该数据库。
        if ([fmdb open]) {
            _db = fmdb;
            _dbPath = path;
            return self;
        }
    }
    NSLog(@"database can not open !");
    return nil;
}




#pragma mark - 创建表

//CREATE TABLE User (CAIID  INTEGER PRIMARY KEY, name TEXT, age INTEGER);
- (NSString *)createTableSQLWithTable:(NSString *)tableName
                           dictionary:(NSDictionary *)parameters
                          excludeName:(NSArray *)nameArr
{
    if (!tableName || tableName.length==0) {
        NSLog(@"syntax error");
        return nil;
    }
    NSMutableString *sqlStr = [NSMutableString stringWithFormat:@"CREATE TABLE %@ (%@  INTEGER PRIMARY KEY", tableName,SQPrimaryId];
    [parameters enumerateKeysAndObjectsUsingBlock:^(id  _Nonnull key, id  _Nonnull obj, BOOL * _Nonnull stop) {
        if (!(nameArr && [nameArr containsObject:key]) && ![key isEqualToString:SQPrimaryId]) {
            
            [sqlStr appendFormat:@", %@ %@",key ,[parameters objectForKey:key]];
        }
    }];
    [sqlStr appendFormat:@");"];
    return [sqlStr copy];
}

- (BOOL)createTable:(NSString *)tableName
                dic:(nullable NSDictionary *)parameters
{
    return [self createTable:tableName dic:parameters error:nil];
}

- (BOOL)createTable:(NSString *)tableName
                dic:(nullable NSDictionary *)parameters
              error:(NSError * __autoreleasing *)error
{
    return [self createTable:tableName dic:parameters excludeName:nil error:error];
}

- (BOOL)createTable:(NSString *)tableName
                dic:(nullable NSDictionary *)parameters
        excludeName:(nullable NSArray<NSString *> *)nameArr
              error:(NSError * __autoreleasing *)error
{
    NSDictionary *dic= parameters;
    NSString *sqlStr = [self createTableSQLWithTable:tableName dictionary:dic excludeName:nameArr];
    BOOL creatFlag = NO;
    creatFlag = [self.db executeUpdate:sqlStr];
    if (!creatFlag) {
        if (error) {
            *error = self.db.lastError;
        }
        NSLog(@"%@",self.db.lastError);
    }
    return creatFlag;
}


// 2.增加的时候，（1）如果指定key的column在表中存在, 则可以新增
// （2）表还没有这个key的column，则不能新增，会报错

#pragma mark - *************** 增删改查表中数据

#pragma mark-增
//INSERT INTO User (name,age) values(?,?)
- (BOOL)insertTable:(NSString *)tableName dic:(id)parameters
{
    NSDictionary *dic;
    if ([parameters isKindOfClass:[NSDictionary class]]) {
        dic = parameters;
    }else {
        return NO;
    }
    NSMutableString *sqlStr = [[NSMutableString alloc] initWithFormat:@"INSERT INTO %@ ", tableName];
    NSMutableArray *keys = [NSMutableArray array];
    NSMutableArray *placeholders = [NSMutableArray array];
    NSMutableArray *values = [NSMutableArray array];
    [dic enumerateKeysAndObjectsUsingBlock:^(id  _Nonnull key, id  _Nonnull obj, BOOL * _Nonnull stop) {
        BOOL exists = [self columnExists:key inTableWithName:tableName];
        if (![key isEqualToString:SQPrimaryId] && exists) {
            [keys addObject:key];
            [placeholders addObject:@"?"];
            [values addObject:obj];
        }
    }];
    if (keys.count >0) {
        NSString *sqlKeys = [keys componentsJoinedByString:@","];
        NSString *sqlPlaceholders = [placeholders componentsJoinedByString:@","];
        [sqlStr appendFormat:@"(%@) values(%@)",sqlKeys,sqlPlaceholders];
    }else{
        NSLog(@"表中没有对应的key");
        return NO;
    }
    BOOL creatFlag = [self.db executeUpdate:sqlStr withArgumentsInArray:values];
    if (!creatFlag) {
        NSLog(@"%@",self.db.lastError);
    }
    return creatFlag;
}


#pragma mark-删

- (BOOL)deleteTable:(NSString *)tableName whereFormat:(NSString *)format,...
{
    //条件语句where
    va_list args;
    va_start(args, format);
    //必须处理，不然空会崩
    NSString *sqlWhere = format ? [[NSString alloc] initWithFormat:format locale:[NSLocale currentLocale] arguments:args]:format;
    va_end(args);
    NSMutableString *sqlStr = [[NSMutableString alloc] initWithFormat:@"DELETE FROM %@ ", tableName];
    [sqlStr appendString:sqlWhere];
    BOOL flag = [self.db executeUpdate:sqlStr];
    if (!flag) {
        NSLog(@"%@",self.db.lastError);
    }
    return flag;
}

- (BOOL)deleteTable:(NSString *)tableName
{
    __block BOOL flag = NO;
    WS(weakSelf);
    [self executeInDatabase:^{
        NSString *sqlStr = [NSString stringWithFormat:@"DELETE FROM %@", tableName];
        flag = [weakSelf.db executeUpdate:sqlStr];
        if (!flag) {
            NSLog(@"%@",weakSelf.db.lastError);
        }
    }];
    return flag;
}

#pragma mark-改

//改整个：UPDATE User SET name = ?,sex = ?
//改某条：更新 ID 为 6 的客户地址：UPDATE User SET sex = 1, name = "123" WHERE name = "海参";
- (BOOL)updateTable:(NSString *)tableName
                dic:(NSDictionary *)parameters
        whereFormat:(NSString *)format, ...
{
    va_list args;
    va_start(args, format);
    NSString *sqlWhere = format ? [[NSString alloc] initWithFormat:format locale:[NSLocale currentLocale] arguments:args]:format;
    va_end(args);
    NSMutableString *sqlStr = [[NSMutableString alloc] initWithFormat:@"UPDATE %@ SET ", tableName];
    NSMutableArray *keys = [NSMutableArray array];
    NSMutableArray *placeholders = [NSMutableArray array];
    NSMutableArray *values = [NSMutableArray array];
    [parameters enumerateKeysAndObjectsUsingBlock:^(id  _Nonnull key, id  _Nonnull obj, BOOL * _Nonnull stop) {
        BOOL exists = [self columnExists:key inTableWithName:tableName];
        if (![key isEqualToString:SQPrimaryId] && exists) {
            [keys addObject:key];
            [placeholders addObject:@"?"];
            [values addObject:obj];
        }
    }];
    if (keys.count >0) {
        NSMutableArray *keyValuesStrMArray = [NSMutableArray array];
        [keys enumerateObjectsUsingBlock:^(id  _Nonnull obj, NSUInteger idx, BOOL * _Nonnull stop) {
            NSString *str = [NSString stringWithFormat:@"%@ = ?",obj];
            [keyValuesStrMArray addObject:str];
        }];
        NSString *sqlKeyValuesStr = [keyValuesStrMArray componentsJoinedByString:@","];
        [sqlStr appendFormat:@"%@",sqlKeyValuesStr];
    }else{
        NSLog(@"表中没有对应的key");
        return NO;
    }
    if (sqlWhere.length) {
        [sqlStr appendFormat:@" %@", sqlWhere];
    }
    BOOL flag = [self.db executeUpdate:sqlStr withArgumentsInArray:values];
    if (!flag) {
        NSLog(@"%@",self.db.lastError);
    }
    return flag;
}



#pragma mark-查
- (NSArray *)fetchTable:(NSString *)tableName
                     dic:(NSDictionary *)parameters
             whereFormat:(NSString *)format, ...
{
    va_list args;
    va_start(args, format);
    NSString *sqlWhere = format ? [[NSString alloc] initWithFormat:format locale:[NSLocale currentLocale] arguments:args]:format;
    va_end(args);
    NSMutableArray *resultMArr = [NSMutableArray arrayWithCapacity:0];
    NSDictionary *dic;
    NSMutableString *sqlStr = [[NSMutableString alloc] initWithFormat:@"SELECT * FROM %@ %@",tableName, sqlWhere?sqlWhere:@""];
    FMResultSet *set = [self.db executeQuery:sqlStr];
    if ([parameters isKindOfClass:[NSDictionary class]])
    {
        dic = parameters;
        while ([set next])
        {
            NSMutableDictionary *resultDic = [NSMutableDictionary dictionaryWithCapacity:0];
            for (NSString *key in dic) {
                if ([dic[key] isEqualToString:SQTEXT]) {
                    id value = [set stringForColumn:key];
                    if (value){
                        [resultDic setObject:value forKey:key];
                    }
                }
                else if ([dic[key] isEqualToString:SQINTEGER]) {
                    [resultDic setObject:@([set longLongIntForColumn:key]) forKey:key];
                }
                else if ([dic[key] isEqualToString:SQREAL]) {
                    [resultDic setObject:[NSNumber numberWithDouble:[set doubleForColumn:key]] forKey:key];
                } else if ([dic[key] isEqualToString:SQBLOB]) {
                    id value = [set dataForColumn:key];
                    if (value){
                        [resultDic setObject:value forKey:key];
                    }
                }
            }
            if (resultDic) {
                [resultMArr addObject:resultDic];
            }
        }
    }
    return resultMArr;
}

#pragma mark-删除表


- (BOOL)dropTable:(NSString *)tableName
{
    NSString *sqlStr = [NSString stringWithFormat:@"DROP TABLE %@", tableName];
    if (![self.db executeUpdate:sqlStr]) {
        return NO;
    }
    return YES;
}


#pragma mark - 条件查找

- (BOOL)tableExists:(NSString *)tableName{
    
    return [self.db tableExists:tableName];
}


/// 测试数据库中特定表的特定列是否存在
/// @param columnName key
/// @param tableName 表
- (BOOL)columnExists:(NSString*)columnName inTableWithName:(NSString*)tableName{
    
    return [self.db columnExists:columnName inTableWithName:tableName];
}

// 得到表里的字段名称
- (NSArray *)getColumnArr:(NSString *)tableName
{
    NSMutableArray *mArr = [NSMutableArray arrayWithCapacity:0];
    FMResultSet *resultSet = [self.db getTableSchema:tableName];
    while ([resultSet next]) {
        [mArr addObject:[resultSet stringForColumn:@"name"]];
    }
    return mArr;
}

- (int)tableItemCount:(NSString *)tableName
{
    NSString *sqlstr = [NSString stringWithFormat:@"SELECT count(*) as 'count' FROM %@", tableName];
    FMResultSet *set = [_db executeQuery:sqlstr];
    while ([set next])
    {
        return [set intForColumn:@"count"];
    }
    return 0;
}


// 获取model的key和value
- (NSDictionary *)getModelPropertyKeyValue:(id)model tableName:(NSString *)tableName
{
    NSMutableDictionary *mDic = [NSMutableDictionary dictionaryWithCapacity:0];
    unsigned int outCount;
    objc_property_t *properties = class_copyPropertyList([model class], &outCount);
    for (int i = 0; i < outCount; i++) {
        NSString *name = [NSString stringWithCString:property_getName(properties[i]) encoding:NSUTF8StringEncoding];
        id value = [model valueForKey:name];
        if (value) {
            [mDic setObject:value forKey:name];
        }
    }
    free(properties);
    return mDic;
}

// =============================   线程安全操作    ===============================

- (void)executeInDatabase:(void(^)(void))block
{
    [[self dbQueue] inDatabase:^(FMDatabase *db) {
        block();
    }];
}

@end



@implementation CAIFMDB(JSONObject)

- (BOOL)createGeneralTable:(NSString *)tableName
                     model:(nullable id)model
                     error:(NSError * __autoreleasing *)error
{
    __block BOOL creatFlag = NO;
    WS(weakSelf);
    [self executeInDatabase:^{
        NSString *sqlStr = [weakSelf createGeneralTableSQLWithTable:tableName];
        creatFlag = [weakSelf.db executeUpdate:sqlStr];
        if (!creatFlag) {
            if (error) {
                *error = weakSelf.db.lastError;
            }
            NSLog(@"%@",weakSelf.db.lastError);
        }
    }];
    return creatFlag;
}

- (NSString *)createGeneralTableSQLWithTable:(NSString *)tableName
{
    if (!tableName || tableName.length==0) {
        NSLog(@"syntax error");
        return nil;
    }
    NSMutableString *sqlStr = [NSMutableString stringWithFormat:@"CREATE TABLE %@ (%@  INTEGER PRIMARY KEY", tableName,SQPrimaryId];
    [sqlStr appendFormat:@", data Text, v INTEGER"];
    [sqlStr appendFormat:@");"];
    return [sqlStr copy];
}

#pragma mark-增改查表中数据

- (BOOL)insertTable:(NSString *)tableName
              model:(id)model
              error:(NSError * __autoreleasing *)error
{
    __block BOOL flag = NO;
    WS(weakSelf);
    [self executeInDatabase:^{
        NSMutableString *sqlStr = [[NSMutableString alloc] initWithFormat:@"INSERT INTO %@ ", tableName];
        [sqlStr appendFormat:@"(data,v) values(?,?)"];
        NSString *jsonStr;
        if ([model isKindOfClass:[NSString class]]){
            jsonStr = model;
        }else{
            jsonStr = [model yy_modelToJSONString];
        }
        NSArray *values = [NSArray arrayWithObjects:jsonStr,@([CAIFMDB caiDBVersion]), nil];
        
        flag = [weakSelf.db executeUpdate:sqlStr withArgumentsInArray:values];
        if (!flag) {
            if (error) {
                *error = weakSelf.db.lastError;
            }
            NSLog(@"%@",weakSelf.db.lastError);
        }
    }];
    return flag;
}

- (BOOL)insertEncodeWithTable:(NSString *)tableName
                            model:(id)model
                            error:(NSError * __autoreleasing *)error
{
    NSString *jsonStr;
    if ([model isKindOfClass:[NSString class]]){
        jsonStr = model;
    }else{
        jsonStr = [model yy_modelToJSONString];
    }
    NSString *encodeStr = [NSData caiDBEncode:jsonStr];
    return [self insertTable:tableName model:encodeStr error:error];
}


- (BOOL)updateTable:(NSString *)tableName
              model:(id)model
{
    __block BOOL flag = NO;
    WS(weakSelf);
    [self executeInDatabase:^{
        NSMutableString *sqlStr = [[NSMutableString alloc] initWithFormat:@"UPDATE %@ SET ", tableName];
        NSString *str = [NSString stringWithFormat:@"data = ?, v = ?"];
        [sqlStr appendFormat:@"%@",str];
        NSString *jsonStr;
        if ([model isKindOfClass:[NSString class]]){
            jsonStr = model;
        }else{
            jsonStr = [model yy_modelToJSONString];
        }
        NSArray *values = [NSArray arrayWithObjects:jsonStr,@([CAIFMDB caiDBVersion]), nil];
        flag = [weakSelf.db executeUpdate:sqlStr withArgumentsInArray:values];
        if (!flag) {
            NSLog(@"%@",weakSelf.db.lastError);
        }
    }];
    return flag;
}

- (BOOL)updateEncodeTable:(NSString *)tableName
                    model:(id)model
{
    NSString *jsonStr;
    if ([model isKindOfClass:[NSString class]]){
        jsonStr = model;
    }else{
        jsonStr = [model yy_modelToJSONString];
    }
    NSString *encodeStr = [NSData caiDBEncode:jsonStr];
    return [self updateTable:tableName model:encodeStr];
}


- (BOOL)setTable:(NSString *)tableName
           model:(id)model
           error:(NSError * __autoreleasing *)error
{
    CAIFMDB *db = [CAIFMDB sharedInstance];
    //如果没有创建表，则先创建
    if (![db tableExists:tableName]) {
        BOOL flag = [self createGeneralTable:tableName model:model error:error];
        if (!flag) {
            return flag;
        }
    }
    //如果有数据，则更新
    if ([db tableItemCount:tableName]>0) {
        return [db updateTable:tableName model:model];
    }
    return  [db insertTable:tableName model:model error:error];
}

- (BOOL)setEncodeTable:(NSString *)tableName
                 model:(id)model
                 error:(NSError * __autoreleasing *)error
{
    CAIFMDB *db = [CAIFMDB sharedInstance];
    //如果没有创建表，则先创建
    if (![db tableExists:tableName]) {
        BOOL flag = [self createGeneralTable:tableName model:model error:error];
        if (!flag) {
            return flag;
        }
    }
    //如果有数据，则更新
    if ([db tableItemCount:tableName]>0) {
        return [self updateEncodeTable:tableName model:model];
    }
    return  [self insertEncodeWithTable:tableName model:model error:error];
}

- (NSArray *)fetchTable:(NSString *)tableName modelClass:(Class)cls
{
    NSMutableArray *resultMArr = [NSMutableArray arrayWithCapacity:0];
    WS(weakSelf);
    [self executeInDatabase:^{
        NSMutableString *sqlStr = [[NSMutableString alloc] initWithFormat:@"SELECT * FROM %@",tableName];
        FMResultSet *set = [weakSelf.db executeQuery:sqlStr];
        while ([set next]) {
            id value = [set stringForColumn:@"data"];
            if (value) {
                id model = [cls yy_modelWithJSON:value];
                if (model) {
                    [resultMArr addObject:model];
                }
            }
        }
    }];
    return resultMArr;
}

- (NSArray *)fetchDecodeTable:(NSString *)tableName modelClass:(Class)cls
{
    NSMutableArray *resultMArr = [NSMutableArray arrayWithCapacity:0];
    WS(weakSelf);
    [self executeInDatabase:^{
        NSMutableString *sqlStr = [[NSMutableString alloc] initWithFormat:@"SELECT * FROM %@",tableName];
        FMResultSet *set = [weakSelf.db executeQuery:sqlStr];
        while ([set next]) {
            id value = [set stringForColumn:@"data"];
            NSString *decodeStr = [NSData caiDBDecode:value];
            if (decodeStr) {
                id model = [cls yy_modelWithJSON:decodeStr];
                [resultMArr addObject:model];
            }
        }
    }];
    return resultMArr;
}


///标记之前存储的版本
+ (NSInteger)caiDBVersion{
    return 1;
}
@end

